package com.mairuide.bill.methodsanalysis;

import java.text.ParseException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.hibernate.SQLQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.mairuide._frame.base.BaseRepository;
import com.mairuide._frame.base.BaseServiceProxy;
import com.mairuide._frame.utils.StringUtils;
import com.mairuide._frame.utils.Testtrans;
import com.mairuide.bill.DealFlow;
import com.mairuide.casemanage.service.CaseManageService;
import com.mairuide.entity.PageResult;

/**
 * 常用战法分析service
 * @author mh
 *
 */
@Service
@Transactional(readOnly = true)
public class CommonStatisticsService extends BaseServiceProxy<CommonStatistics> {
	
	@Override
	protected BaseRepository<CommonStatistics> getBaseRepository() {
		return null;
	}
	@PersistenceContext
	private EntityManager entityManager;

	@Override
	protected EntityManager getEntityManager() {
		return entityManager;
	}
	
	@Autowired
    private JdbcTemplate jdbcTemplate;
	@Autowired
	private CaseManageService caseManageService;

	/**
	 * 分页查询共同账户战法分析
	 * @param csp
	 * @param page
	 * @return
	 */
	public Page<CommonStatistics> findCommonAccountByPage(CommonStatisticsParams csp, Pageable page,String sortName,String sortOrder) {
		String whichTable = caseManageService.getWhichTable(csp.getCaseId());
		List<Object> params = new LinkedList<Object>();
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT ");
		selectSql.append("(case when  MAX(TDF.ACCOUNT_RIVAL) is not null then  MAX(TDF.ACCOUNT_RIVAL) else TDF.ACCOUNT_RIVAL_NUMBER end) as commonAccountRival,");
		selectSql.append("TDF.ACCOUNT_RIVAL_NUMBER AS commonAccountRivalNumber,");
		selectSql.append("COUNT (*) AS occurrences,");
		selectSql.append("SUM (TDF.ACCOUNT_MONEY) AS sumMoney,");
		selectSql.append("COUNT (DISTINCT TDF.ACCOUNT_BODY_CARD) ariseObject ");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append("FROM "+whichTable+" TDF where 1=1 AND IS_DELETE = 0  AND TDF.ACCOUNT_RIVAL_NUMBER IS NOT NULL ");
		/* 案件id */
		if(StringUtils.isNotEmpty(csp.getCaseId())) {
			fromWhereSql.append("AND TDF.CASEMANAGE_ID =? ");
			params.add(csp.getCaseId());
		}
		/* 交易主题卡号 */
		fromWhereSql.append(" AND (1=0 ");
		if(StringUtils.isNotEmpty(csp.getCheckedAccount())) {
			if (csp.getCheckedAccount().split(",").length < 1000) {
				String cards = "";
				for (String str : csp.getCheckedAccount().split(",")) {
					if (StringUtils.isNotEmpty(str)) {
						String accountBodyCard = str.split("_")[1];
						if (StringUtils.isNotEmpty(accountBodyCard)) {
							cards = cards + "'" + accountBodyCard + "',";
						}
					}
				}
				if (cards.endsWith(",")) {
					cards = cards.substring(0, cards.length() - 1);
				}
				if (cards.length() > 0) {
					fromWhereSql.append(" OR TDF.ACCOUNT_BODY_CARD IN (" + cards + ")");
				}
			}else {
				fromWhereSql.append(" or 1=1");
			}
		}
		fromWhereSql.append(" ) ");
		
		/* 交易开始日期 */
		if(StringUtils.isNotEmpty(csp.getStarTime())) {
			fromWhereSql.append(" AND TDF.TRADE_DATE >= to_date('"+csp.getStarTime()+"','yyyy-mm-dd hh24:mi:ss') ");
		}
		
		/* 交易结束日期 */
		if(StringUtils.isNotEmpty(csp.getEndTime())) {
			fromWhereSql.append(" AND TDF.TRADE_DATE <= to_date('"+csp.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
		}
		fromWhereSql.append("GROUP BY TDF.ACCOUNT_RIVAL_NUMBER ");
		fromWhereSql.append("HAVING COUNT(DISTINCT TDF.ACCOUNT_BODY_CARD) >1 ");
		/* 模糊查询共同交易对手账号*/
		if(StringUtils.isNotEmpty(csp.getCommonAccountRivalNumber())) {
			fromWhereSql.append("AND TDF.ACCOUNT_RIVAL_NUMBER like '%"+csp.getCommonAccountRivalNumber()+"%' ");
		}
		/* 模糊查询共同交易对手名称*/
		if(StringUtils.isNotEmpty(csp.getAccountRival())) {
			fromWhereSql.append("AND TDF.ACCOUNT_RIVAL like '%"+csp.getAccountRival()+"%' ");
		}
		String orderBySql = null;
		if (org.apache.commons.lang3.StringUtils.isNotBlank(sortName)){
			orderBySql =" ORDER BY "+sortName+" "+sortOrder;
		}else {
			orderBySql = "ORDER BY sumMoney DESC";
		}
		//封装返回数据
		return super.findPageBySql4GroupBy(CommonStatistics.class,selectSql.toString(), fromWhereSql.toString(), orderBySql, params, page);
	}
	
	/**
	 * 分页查询共同账户战法分析
	 * @param csp
	 * @param page
	 * @return
	 */
	public PageResult findCommonAccountByPageEasyUi(CommonStatisticsParams csp) {
		String whichTable = caseManageService.getWhichTable(csp.getCaseId());
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT * FROM (SELECT ");
		selectSql.append("(case when  MAX(TDF.ACCOUNT_RIVAL) is not null then  MAX(TDF.ACCOUNT_RIVAL) else TDF.ACCOUNT_RIVAL_CARD end) as commonAccountRival,");
		selectSql.append("TDF.ACCOUNT_RIVAL_CARD AS commonAccountRivalNumber,");
		selectSql.append("COUNT (*) AS occurrences,");
		selectSql.append("SUM (TDF.ACCOUNT_MONEY) AS sumMoney,");
		selectSql.append("COUNT (DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) ariseObject ");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append("FROM "+whichTable+" TDF where 1=1 AND IS_DELETE = 0  AND TDF.ACCOUNT_RIVAL_CARD IS NOT NULL ");
		/* 案件id */
		if(StringUtils.isNotEmpty(csp.getCaseId())) {
			fromWhereSql.append("AND TDF.CASEMANAGE_ID ='"+csp.getCaseId() +"' ");
		}
		/* 交易主题卡号 */
		fromWhereSql.append(" AND (1=0 ");
		if(StringUtils.isNotEmpty(csp.getCheckedAccount())) {
			if (csp.getCheckedAccount().split(",").length < 1000) {
				String cards = "";
				for (String str : csp.getCheckedAccount().split(",")) {
					if (StringUtils.isNotEmpty(str)) {
						String accountBodyCard = str.split("_")[1];
						if (StringUtils.isNotEmpty(accountBodyCard)) {
							cards = cards + "'" + accountBodyCard + "',";
						}
					}
				}
				if (cards.endsWith(",")) {
					cards = cards.substring(0, cards.length() - 1);
				}
				if (cards.length() > 0) {
					fromWhereSql.append(" OR TDF.ACCOUNT_BODY_CARD IN (" + cards + ")");
				}
			}else {
				fromWhereSql.append(" or 1=1");
			}
		}
		fromWhereSql.append(" ) ");
		
		/* 交易开始日期 */
		if(StringUtils.isNotEmpty(csp.getStarTime())) {
			fromWhereSql.append(" AND TDF.TRADE_DATE >= to_date('"+csp.getStarTime()+"','yyyy-mm-dd hh24:mi:ss') ");
		}
		
		/* 交易结束日期 */
		if(StringUtils.isNotEmpty(csp.getEndTime())) {
			fromWhereSql.append(" AND TDF.TRADE_DATE <= to_date('"+csp.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
		}
		fromWhereSql.append("GROUP BY TDF.ACCOUNT_RIVAL_CARD ");
		fromWhereSql.append("HAVING COUNT(DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) >1 ");
		/* 模糊查询共同交易对手账号*/
		if(StringUtils.isNotEmpty(csp.getCommonAccountRivalNumber())) {
			fromWhereSql.append("AND TDF.ACCOUNT_RIVAL_NUMBER like '%"+csp.getCommonAccountRivalNumber()+"%' ");
		}
		if (StringUtils.isNotEmpty(csp.getSort())){
			fromWhereSql.append(" ORDER BY "+csp.getSort()+" "+csp.getOrder());
		}else {
			fromWhereSql.append("ORDER BY sumMoney DESC");
		}
		fromWhereSql.append(") b ");
		/* 模糊查询共同交易对手名称*/
		if(StringUtils.isNotEmpty(csp.getAccountRival())) {
			fromWhereSql.append(" WHERE b.commonAccountRival like '%"+csp.getAccountRival()+"%' ");
		};
		long count = jdbcTemplate.queryForObject("select count(1) from ("+selectSql.toString()+fromWhereSql.toString()+") a", long.class);
		Query query = this.entityManager.createNativeQuery(selectSql.toString()+fromWhereSql.toString());
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(CommonStatistics.class));
		// 设置分页
		query.setFirstResult((csp.getPage() - 1) * csp.getRows());
		query.setMaxResults(csp.getRows());
		// 获取查询结果集
		List<DealFlow> list = query.getResultList();
		PageResult pageResult = new PageResult(count, list);
		return pageResult;
	}
	
	/**
	 * 根据案件id、交易对手账户分页查询交易详情
	 * @param csp
	 * @param page
	 * @return
	 * @throws ParseException 
	 */
	public PageResult findBillDetailsByCaseIdAndAccountRivalNumberEasyUi(CommonStatisticsParams csp) throws ParseException {
		String whichTable = caseManageService.getWhichTable(csp.getCaseId());
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT TDF.ACCOUNT_BODY_NUMBER AS accountBodyNumber,TDF.ACCOUNT_BODY AS accountBody,TDF.ACCOUNT_BODY_CARD AS accountBodyCard,TDF.ACCOUNT_RIVAL_NUMBER AS accountRivalNumber,");
		selectSql.append("TDF.ACCOUNT_RIVAL AS accountRival,TDF.ACCOUNT_RIVAL_CARD AS accountRivalCard,TDF.ACCOUNT_RIVAL_BANK AS accountRivalBank,TDF.ACCOUNT_ADDRESS AS accountAddress,");
		selectSql.append("TDF.SIGN AS sign,TDF.ACCOUNT_MONEY AS accountMoney,TDF.SURPLUS_MONEY AS surplusMoney,TDF.TRADE_DATE AS tradeDate,TDF.IP_ADDRESS AS ipAddress,");
		selectSql.append("TDF.ACCOUNT_MODE AS accountMode,TDF.CONTENT_ABSTRACT as contentAbstract,TDF.CURRENCY AS currency,TDF.REMARKS AS remarks ");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append("FROM "+whichTable+" TDF where 1=1 AND IS_DELETE = 0 ");
		/* 案件id */
		if(StringUtils.isNotEmpty(csp.getCaseId())) {
			fromWhereSql.append("AND TDF.CASEMANAGE_ID ='"+csp.getCaseId() +"' ");
		}
		/* 收付标志 */
		if(StringUtils.isNotEmpty(csp.getSign())) {
			fromWhereSql.append("AND TDF.SIGN = ='"+csp.getSign() +"' ");
		}
		/* 交易主题卡号 */
		fromWhereSql.append(" AND (1=0 ");
		if(StringUtils.isNotEmpty(csp.getCheckedAccount())) {
			if (csp.getCheckedAccount().split(",").length<1000) {
				String cards = "";
				for (String str : csp.getCheckedAccount().split(",")) {
					if (StringUtils.isNotEmpty(str)) {
						String accountBodyCard = str.split("_")[1];
						if (StringUtils.isNotEmpty(accountBodyCard)) {
							cards = cards + "'" + accountBodyCard + "',";
						}
					}
				}
				if (cards.endsWith(",")) {
					cards = cards.substring(0, cards.length() - 1);
				}
				if (cards.length() > 0) {
					fromWhereSql.append(" OR TDF.ACCOUNT_BODY_CARD IN (" + cards + ")");
				}
			}else {
				fromWhereSql.append(" or 1=1");
			}
		}
		fromWhereSql.append(" ) ");
		
		/* 交易开始日期 */
		if(StringUtils.isNotEmpty(csp.getStarTime())) {
			fromWhereSql.append(" AND TDF.TRADE_DATE >= to_date('"+csp.getStarTime()+"','yyyy-mm-dd hh24:mi:ss') AND TDF.TRADE_DATE IS NOT NULL ");
		}
		
		/* 交易结束日期 */
		if(StringUtils.isNotEmpty(csp.getEndTime())) {
			fromWhereSql.append(" AND TDF.TRADE_DATE <= to_date('"+csp.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') AND TDF.TRADE_DATE IS NOT NULL ");
		}
		
		/* 交易对手账号 */
		if(StringUtils.isNotEmpty(csp.getAccountRivalNumber())) {
			fromWhereSql.append("AND TDF.ACCOUNT_RIVAL_NUMBER = '"+csp.getAccountRivalNumber()+"' AND TDF.ACCOUNT_RIVAL_NUMBER IS NOT NULL ");
		}
		/* 交易对手户名 */
		if(StringUtils.isNotEmpty(csp.getAccountRival())) {
			fromWhereSql.append("AND TDF.ACCOUNT_RIVAL = '"+csp.getAccountRival()+"' AND TDF.ACCOUNT_RIVAL IS NOT NULL ");
		}
		/* 共同交易日期 */
		if(StringUtils.isNotEmpty(csp.getAriseDate())) {
			fromWhereSql.append(" AND TO_CHAR(TDF.TRADE_DATE,'YYYY-MM-DD')='"+csp.getAriseDate()+"'  AND TDF.TRADE_DATE IS NOT NULL ");
		}
		/* 共同交易金额 */
		if(StringUtils.isNotEmpty(csp.getAriseMoney())) {
			fromWhereSql.append(" AND TDF.ACCOUNT_MONEY = '"+csp.getAriseMoney()+"' AND ACCOUNT_MONEY IS NOT NULL ");
		}
		/* 共同机构号 */
		if(StringUtils.isNotEmpty(csp.getOrganizationNumber())) {
			fromWhereSql.append(" AND TDF.TRADING_ORGANIZATION_NUMBER = '"+csp.getOrganizationNumber()+"' AND TDF.TRADING_ORGANIZATION_NUMBER IS NOT NULL ");
		}
		/* 共同机构名称 */
		if(StringUtils.isNotEmpty(csp.getOrganizationName())) {
			fromWhereSql.append(" AND TDF.ACCOUNT_RIVAL_BANK = '"+csp.getOrganizationName()+"' AND TDF.ACCOUNT_RIVAL_BANK IS NOT NULL ");
		}
		/* 共同柜员号 */
		if(StringUtils.isNotEmpty(csp.getTellerNumber())) {
			fromWhereSql.append(" AND TDF.TELLER_NUMBER = '"+csp.getTellerNumber()+"' AND TDF.TELLER_NUMBER IS NOT NULL ");
		}
        if (StringUtils.isNotEmpty(csp.getSort())){
        	fromWhereSql.append(" order by "+csp.getSort()+" "+csp.getOrder());
        }else {
        	fromWhereSql.append("ORDER BY accountMoney DESC");
        }
        
        long count = jdbcTemplate.queryForObject("select count(1) from ("+selectSql.toString()+fromWhereSql.toString()+") a", long.class);
		Query query = this.entityManager.createNativeQuery(selectSql.toString()+fromWhereSql.toString());
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(DealFlow.class));
		// 设置分页
		query.setFirstResult((csp.getPage() - 1) * csp.getRows());
		query.setMaxResults(csp.getRows());
		// 获取查询结果集
		List<DealFlow> list = query.getResultList();
		PageResult pageResult = new PageResult(count, list);
		return pageResult;

	}
	
	
	/**
	 * 查询所有共同账户战法分析
	 * @param csp
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<CommonStatistics> findCommonAccountAll(CommonStatisticsParams csp) {
		String whichTable = caseManageService.getWhichTable(csp.getCaseId());
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT * FROM (SELECT (case when  MAX(TDF.ACCOUNT_RIVAL) is not null then  MAX(TDF.ACCOUNT_RIVAL) else TDF.ACCOUNT_RIVAL_CARD end) as commonAccountRival ,");
		selectSql.append("COUNT (*) AS occurrences,");
		selectSql.append("SUM (TDF.ACCOUNT_MONEY) AS sumMoney,");
		selectSql.append("COUNT (DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) ariseObject ");
		selectSql.append("FROM "+whichTable+" TDF where 1=1 AND IS_DELETE = 0  AND TDF.ACCOUNT_RIVAL_CARD IS NOT NULL ");
		/* 案件id */
		if(StringUtils.isNotEmpty(csp.getCaseId())) {
			selectSql.append("AND TDF.CASEMANAGE_ID ='"+csp.getCaseId()+"' ");
		}
		/* 收付标志 */
		if(StringUtils.isNotEmpty(csp.getSign())) {
			selectSql.append(" AND TDF.SIGN>='"+csp.getSign()+"' ");
		}
		/* 交易主题卡号 */
		selectSql.append(" AND (1=0 ");
		if(StringUtils.isNotEmpty(csp.getCheckedAccount())) {
			if (csp.getCheckedAccount().split(",").length<1000) {
				String cards = "";
				for (String str : csp.getCheckedAccount().split(",")) {
					if (StringUtils.isNotEmpty(str)) {
						String accountBodyCard = str.split("_")[1];
						if (StringUtils.isNotEmpty(accountBodyCard)) {
							cards = cards + "'" + accountBodyCard + "',";
						}
					}
				}
				if (cards.endsWith(",")) {
					cards = cards.substring(0, cards.length() - 1);
				}
				if (cards.length() > 0) {
					selectSql.append(" OR TDF.ACCOUNT_BODY_CARD IN (" + cards + ")");
				}
			}else {
				selectSql.append(" or 1=1");
			}
		}
		selectSql.append(" ) ");
		
		/* 交易开始日期 */
		if(StringUtils.isNotEmpty(csp.getStarTime())) {
			selectSql.append(" AND TDF.TRADE_DATE >= to_date('"+csp.getStarTime()+"','yyyy-mm-dd hh24:mi:ss') ");
		}
		
		/* 交易结束日期 */
		if(StringUtils.isNotEmpty(csp.getEndTime())) {
			selectSql.append(" AND TDF.TRADE_DATE <= to_date('"+csp.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
		}
		selectSql.append("GROUP BY TDF.ACCOUNT_RIVAL_CARD ");
		selectSql.append("HAVING COUNT(DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) >1 ");
		/* 模糊查询共同交易对手账号*/
		if(StringUtils.isNotEmpty(csp.getCommonAccountRivalNumber())) {
			selectSql.append(" AND TDF.ACCOUNT_RIVAL_NUMBER like '%"+csp.getCommonAccountRivalNumber()+"%' ");
		}
		selectSql.append(" ORDER BY occurrences DESC ) b ");
		/* 模糊查询共同交易对手名称*/
		if(StringUtils.isNotEmpty(csp.getAccountRival())) {
			selectSql.append(" WHERE b.commonAccountRival like '%"+csp.getAccountRival()+"%' ");
		}
		Query query = getEntityManager().createNativeQuery(selectSql.toString());
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(CommonStatistics.class));
		//封装返回数据
		return query.getResultList();
	}
	
	/**
	 * 分页查询共同户名战法分析
	 * @param csp
	 * @param page
	 * @return
	 */
	public PageResult findCommonAccountRivalByPageEasyUi(CommonStatisticsParams csp) {
		String whichTable = caseManageService.getWhichTable(csp.getCaseId());
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT TDF.ACCOUNT_RIVAL AS commonAccountRival,");
		selectSql.append("COUNT (*) AS occurrences,");
		selectSql.append("COUNT (DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) ariseObject,");
		selectSql.append("SUM (TDF.ACCOUNT_MONEY) AS sumMoney ");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append("FROM "+whichTable+" TDF where 1=1 AND IS_DELETE = 0 AND TDF.ACCOUNT_RIVAL IS NOT NULL ");
		/* 案件id */
		if(StringUtils.isNotEmpty(csp.getCaseId())) {
			fromWhereSql.append("AND TDF.CASEMANAGE_ID ='"+csp.getCaseId()+"' ");
		}
		/* 收付标志 */
		if(StringUtils.isNotEmpty(csp.getSign())) {
			fromWhereSql.append("AND TDF.SIGN = '"+csp.getSign()+"' ");
		}
		
		/* 交易主题卡号 */
		fromWhereSql.append(" AND (1=0 ");
		if(StringUtils.isNotEmpty(csp.getCheckedAccount())) {
			if (csp.getCheckedAccount().split(",").length<1000) {
				String cards = "";
				for (String str : csp.getCheckedAccount().split(",")) {
					if (StringUtils.isNotEmpty(str)) {
						String accountBodyCard = str.split("_")[1];
						if (StringUtils.isNotEmpty(accountBodyCard)) {
							cards = cards + "'" + accountBodyCard + "',";
						}
					}
				}
				if (cards.endsWith(",")) {
					cards = cards.substring(0, cards.length() - 1);
				}
				if (cards.length() > 0) {
					fromWhereSql.append(" OR TDF.ACCOUNT_BODY_CARD IN (" + cards + ")");
				}
			}else {
				fromWhereSql.append(" or 1=1");
			}
		}
		fromWhereSql.append(" ) ");
		
		/* 交易开始日期 */
		if(StringUtils.isNotEmpty(csp.getStarTime())) {
			fromWhereSql.append(" AND TDF.TRADE_DATE >= to_date('"+csp.getStarTime()+"','yyyy-mm-dd hh24:mi:ss') ");
		}
		
		/* 交易结束日期 */
		if(StringUtils.isNotEmpty(csp.getEndTime())) {
			fromWhereSql.append(" AND TDF.TRADE_DATE <= to_date('"+csp.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
		}
		
		fromWhereSql.append("GROUP BY TDF.ACCOUNT_RIVAL HAVING COUNT(DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) > 1 ");
		/* 模糊查询共同交易对手名称*/
		if(StringUtils.isNotEmpty(csp.getAccountRival())) {
			fromWhereSql.append("AND TDF.ACCOUNT_RIVAL like '%"+csp.getAccountRival()+"%' ");
		}
		if (StringUtils.isNotEmpty(csp.getSort())){
			fromWhereSql.append(" order by "+csp.getSort()+" "+csp.getOrder());
		}else {
			fromWhereSql.append("ORDER BY sumMoney DESC");
		}
		long count = jdbcTemplate.queryForObject("select count(1) from ("+selectSql.toString()+fromWhereSql.toString()+") a", long.class);
		Query query = this.entityManager.createNativeQuery(selectSql.toString()+fromWhereSql.toString());
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(CommonStatistics.class));
		// 设置分页
		query.setFirstResult((csp.getPage() - 1) * csp.getRows());
		query.setMaxResults(csp.getRows());
		// 获取查询结果集
		List<DealFlow> list = query.getResultList();
		PageResult pageResult = new PageResult(count, list);
		return pageResult;
	}
	
	/**
	 * 分页查询共同户名战法分析
	 * @param csp
	 * @param page
	 * @return
	 */
	public Page<CommonStatistics> findCommonAccountRivalByPage(CommonStatisticsParams csp, Pageable page,String sortName,String sortOrder) {
		String whichTable = caseManageService.getWhichTable(csp.getCaseId());
		List<Object> params = new LinkedList<Object>();
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT TDF.ACCOUNT_RIVAL AS commonAccountRival,");
		selectSql.append("COUNT (*) AS occurrences,");
		selectSql.append("COUNT (DISTINCT TDF.ACCOUNT_RIVAL) ariseObject,");
		selectSql.append("SUM (TDF.ACCOUNT_MONEY) AS sumMoney ");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append("FROM "+whichTable+" TDF where 1=1 AND IS_DELETE = 0 AND TDF.ACCOUNT_RIVAL IS NOT NULL ");
		appendWhereSql4Params(csp, params, fromWhereSql);
		
		fromWhereSql.append("GROUP BY TDF.ACCOUNT_RIVAL HAVING COUNT(DISTINCT TDF.ACCOUNT_BODY_CARD) > 1 ");
		/* 模糊查询共同交易对手名称*/
		if(StringUtils.isNotEmpty(csp.getAccountRival())) {
			fromWhereSql.append("AND TDF.ACCOUNT_RIVAL like '%"+csp.getAccountRival()+"%' ");
		}
		String orderBySql = null;
		if (org.apache.commons.lang3.StringUtils.isNotBlank(sortName)){
			orderBySql =" order by "+sortName+" "+sortOrder;
		}else {
			orderBySql = "ORDER BY sumMoney DESC";
		}
		//封装返回数据
		return super.findPageBySql4GroupBy(CommonStatistics.class,selectSql.toString(), fromWhereSql.toString(), orderBySql, params, page);

	}
	
	/**
	 * 查询所有共同户名战法分析
	 * @param csp
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<CommonStatistics> findCommonAccountRivalAll(CommonStatisticsParams csp) {
		String whichTable = caseManageService.getWhichTable(csp.getCaseId());
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT ");
		selectSql.append("(case when TDF.ACCOUNT_RIVAL is not null then TDF.ACCOUNT_RIVAL else MAX(TDF.ACCOUNT_RIVAL_NUMBER) end) as commonAccountRival ,");
		selectSql.append("COUNT (*) AS occurrences,");
		selectSql.append("COUNT (DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) ariseObject,");
		selectSql.append("SUM (TDF.ACCOUNT_MONEY) AS sumMoney ");
		selectSql.append("FROM "+whichTable+" TDF where 1=1 AND IS_DELETE = 0  AND TDF.ACCOUNT_RIVAL IS NOT NULL ");
		appendWhereSql(csp, selectSql);
		
		selectSql.append("GROUP BY TDF.ACCOUNT_RIVAL HAVING COUNT (*) > 1 AND COUNT(DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) >1 ");
		/* 模糊查询共同交易对手名称*/
		if(StringUtils.isNotEmpty(csp.getAccountRival())) {
			selectSql.append("AND TDF.ACCOUNT_RIVAL like '%"+csp.getAccountRival()+"%' ");
		}
		selectSql.append("ORDER BY occurrences DESC");
		Query query = getEntityManager().createNativeQuery(selectSql.toString());
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(CommonStatistics.class));
		//封装返回数据
		return query.getResultList();
	}
	
	/**
	 * 分页查询同天同金额战法分析
	 * @param csp
	 * @param page
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public PageResult findCommonDayAndMoneyByPageEasyUi(CommonStatisticsParams csp) {
		String whichTable = caseManageService.getWhichTable(csp.getCaseId());
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT TO_CHAR(TDF.TRADE_DATE,'YYYY-MM-DD') AS ariseDate,");
		selectSql.append("TDF.ACCOUNT_MONEY AS ariseMoney,");
		selectSql.append("COUNT (*) AS occurrences,");
		selectSql.append("COUNT (DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) ariseObject,");
		selectSql.append("SUM (TDF.ACCOUNT_MONEY) AS sumMoney ");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append("FROM "+whichTable+" TDF where 1=1 AND IS_DELETE = 0  AND TDF.TRADE_DATE IS NOT NULL AND TDF.ACCOUNT_MONEY IS NOT NULL AND TDF.ACCOUNT_BODY_CARD IS NOT NULL AND TDF.ACCOUNT_RIVAL_CARD IS NOT NULL ");
		/* 案件id */
		if(StringUtils.isNotEmpty(csp.getCaseId())) {
			fromWhereSql.append("AND TDF.CASEMANAGE_ID ='"+csp.getCaseId()+"' ");
		}
		/* 收付标志 */
		if(StringUtils.isNotEmpty(csp.getSign())) {
			fromWhereSql.append("AND TDF.SIGN = '"+csp.getSign()+"' ");
		}
		
		/* 交易主题卡号 */
		fromWhereSql.append(" AND (1=0 ");
		if(StringUtils.isNotEmpty(csp.getCheckedAccount())) {
			if (csp.getCheckedAccount().split(",").length<1000) {
				String cards = "";
				for (String str : csp.getCheckedAccount().split(",")) {
					if (StringUtils.isNotEmpty(str)) {
						String accountBodyCard = str.split("_")[1];
						if (StringUtils.isNotEmpty(accountBodyCard)) {
							cards = cards + "'" + accountBodyCard + "',";
						}
					}
				}
				if (cards.endsWith(",")) {
					cards = cards.substring(0, cards.length() - 1);
				}
				if (cards.length() > 0) {
					fromWhereSql.append(" OR TDF.ACCOUNT_BODY_CARD IN (" + cards + ")");
				}
			}else {
				fromWhereSql.append(" or 1=1");
			}
		}
		fromWhereSql.append(" ) ");
		
		/* 交易开始日期 */
		if(StringUtils.isNotEmpty(csp.getStarTime())) {
			fromWhereSql.append(" AND TDF.TRADE_DATE >= to_date('"+csp.getStarTime()+"','yyyy-mm-dd hh24:mi:ss') ");
		}
		
		/* 交易结束日期 */
		if(StringUtils.isNotEmpty(csp.getEndTime())) {
			fromWhereSql.append(" AND TDF.TRADE_DATE <= to_date('"+csp.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
		}	
		fromWhereSql.append("GROUP BY TO_CHAR(TDF.TRADE_DATE,'YYYY-MM-DD'),TDF.ACCOUNT_MONEY HAVING COUNT (*) > 1 AND COUNT(DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) >1 ");
        if (StringUtils.isNotEmpty(csp.getSort())){
        	fromWhereSql.append(" order by "+csp.getSort()+" "+csp.getOrder());
        }else {
        	fromWhereSql.append("ORDER BY ariseDate asc");
        }
        CommonStatistics s = null;
		//封装返回数据
        long count = jdbcTemplate.queryForObject("select count(1) from ("+selectSql.toString()+fromWhereSql.toString()+") a", long.class);
		Query query = this.entityManager.createNativeQuery(selectSql.toString()+fromWhereSql.toString());
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(CommonStatistics.class));
		// 设置分页
		query.setFirstResult((csp.getPage() - 1) * csp.getRows());
		query.setMaxResults(csp.getRows());
		// 获取查询结果集
		List<DealFlow> list = query.getResultList();
		PageResult pageResult = new PageResult(count, list);
		return pageResult;

	}
	
	/**
	 * 查询所有同天同金额战法分析
	 * @param csp
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<CommonStatistics> findCommonDayAndMoneyAll(CommonStatisticsParams csp) {
		String whichTable = caseManageService.getWhichTable(csp.getCaseId());
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT TO_CHAR(TDF.TRADE_DATE,'YYYY-MM-DD') AS ariseDate,");
		selectSql.append("TDF.ACCOUNT_MONEY AS ariseMoney,");
		selectSql.append("COUNT (*) AS occurrences,");
		selectSql.append("COUNT (DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) ariseObject,");
		selectSql.append("SUM (TDF.ACCOUNT_MONEY) AS sumMoney ");
		selectSql.append("FROM "+whichTable+" TDF where 1=1 AND IS_DELETE = 0  AND TDF.TRADE_DATE IS NOT NULL and TDF.ACCOUNT_BODY_CARD IS NOT NULL AND TDF.ACCOUNT_RIVAL_CARD IS NOT NULL ");
		appendWhereSql(csp, selectSql);
		selectSql.append("GROUP BY TDF.ACCOUNT_MONEY,TO_CHAR(TDF.TRADE_DATE,'YYYY-MM-DD') HAVING COUNT (*) > 1 AND COUNT(DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) >1 ");
		selectSql.append("ORDER BY occurrences DESC");
		Query query = getEntityManager().createNativeQuery(selectSql.toString());
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(CommonStatistics.class));
		//封装返回数据
		return query.getResultList();
	}
	
	/**
	 * 分页查询同天同地点战法分析
	 * @param csp
	 * @param page
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public PageResult findCommonDayAndAddressByPageEasyUi(CommonStatisticsParams csp) {
		String whichTable = caseManageService.getWhichTable(csp.getCaseId());
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT TO_CHAR(TDF.TRADE_DATE,'YYYY-MM-DD') AS ariseDate,");
		selectSql.append("TDF.TRADING_ORGANIZATION_NUMBER AS organizationNumber,");
		selectSql.append("TDF.ACCOUNT_RIVAL_BANK AS organizationName,");
		selectSql.append("COUNT (*) AS occurrences,");
		selectSql.append("COUNT (DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) ariseObject,");
		selectSql.append("SUM (TDF.ACCOUNT_MONEY) AS sumMoney ");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append("FROM "+whichTable+" TDF where 1=1 AND IS_DELETE = 0  AND TDF.TRADE_DATE IS NOT NULL ");
		fromWhereSql.append("AND TDF.TRADING_ORGANIZATION_NUMBER IS NOT NULL ");
		fromWhereSql.append("AND TDF.ACCOUNT_RIVAL_BANK IS NOT NULL ");
		/* 案件id */
		if(StringUtils.isNotEmpty(csp.getCaseId())) {
			fromWhereSql.append("AND TDF.CASEMANAGE_ID ='"+csp.getCaseId()+"' ");
		}
		/* 收付标志 */
		if(StringUtils.isNotEmpty(csp.getSign())) {
			fromWhereSql.append("AND TDF.SIGN = '"+csp.getSign()+"' ");
		}
		
		/* 交易主题卡号 */
		fromWhereSql.append(" AND (1=0 ");
		if(StringUtils.isNotEmpty(csp.getCheckedAccount())) {
			if (csp.getCheckedAccount().split(",").length<1000) {
				String cards = "";
				for (String str : csp.getCheckedAccount().split(",")) {
					if (StringUtils.isNotEmpty(str)) {
						String accountBodyCard = str.split("_")[1];
						if (StringUtils.isNotEmpty(accountBodyCard)) {
							cards = cards + "'" + accountBodyCard + "',";
						}
					}
				}
				if (cards.endsWith(",")) {
					cards = cards.substring(0, cards.length() - 1);
				}
				if (cards.length() > 0) {
					fromWhereSql.append(" OR TDF.ACCOUNT_BODY_CARD IN (" + cards + ")");
				}
			}else {
				fromWhereSql.append(" or 1=1");
			}
		}
		fromWhereSql.append(" ) ");
		
		/* 交易开始日期 */
		if(StringUtils.isNotEmpty(csp.getStarTime())) {
			fromWhereSql.append(" AND TDF.TRADE_DATE >= to_date('"+csp.getStarTime()+"','yyyy-mm-dd hh24:mi:ss') ");
		}
		
		/* 交易结束日期 */
		if(StringUtils.isNotEmpty(csp.getEndTime())) {
			fromWhereSql.append(" AND TDF.TRADE_DATE <= to_date('"+csp.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
		}
		fromWhereSql.append("GROUP BY TO_CHAR(TDF.TRADE_DATE,'YYYY-MM-DD'),TDF.TRADING_ORGANIZATION_NUMBER,TDF.ACCOUNT_RIVAL_BANK HAVING COUNT (*) > 1 AND COUNT(DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) >1 ");
        if (StringUtils.isNotEmpty(csp.getSort())){
        	fromWhereSql.append(" order by "+csp.getSort()+" "+csp.getOrder());
        }else {
        	fromWhereSql.append("ORDER BY ariseDate asc");
        }
        
        long count = jdbcTemplate.queryForObject("select count(1) from ("+selectSql.toString()+fromWhereSql.toString()+") a", long.class);
		Query query = this.entityManager.createNativeQuery(selectSql.toString()+fromWhereSql.toString());
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(CommonStatistics.class));
		// 设置分页
		query.setFirstResult((csp.getPage() - 1) * csp.getRows());
		query.setMaxResults(csp.getRows());
		// 获取查询结果集
		List<DealFlow> list = query.getResultList();
		PageResult pageResult = new PageResult(count, list);
		return pageResult;
	}
	
	/**
	 * 查询所有同天同地点战法分析
	 * @param csp
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<CommonStatistics> findCommonDayAndAddressAll(CommonStatisticsParams csp) {
		String whichTable = caseManageService.getWhichTable(csp.getCaseId());
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT TO_CHAR(TDF.TRADE_DATE,'YYYY-MM-DD') AS ariseDate,");
		selectSql.append("TDF.TRADING_ORGANIZATION_NUMBER AS organizationNumber,");
		selectSql.append("TDF.ACCOUNT_RIVAL_BANK AS organizationName,");
		selectSql.append("COUNT (*) AS occurrences,");
		selectSql.append("COUNT (DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) ariseObject,");
		selectSql.append("SUM (TDF.ACCOUNT_MONEY) AS sumMoney ");
		selectSql.append("FROM "+whichTable+" TDF where 1=1 AND IS_DELETE = 0  AND TDF.TRADE_DATE IS NOT NULL ");
		selectSql.append("AND TDF.TRADING_ORGANIZATION_NUMBER IS NOT NULL ");
		selectSql.append("AND TDF.ACCOUNT_RIVAL_BANK IS NOT NULL ");
		appendWhereSql(csp, selectSql);
		selectSql.append("GROUP BY TO_CHAR(TDF.TRADE_DATE,'YYYY-MM-DD'),TDF.TRADING_ORGANIZATION_NUMBER,TDF.ACCOUNT_RIVAL_BANK HAVING COUNT (*) > 1 AND COUNT(DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) >1 ");
		selectSql.append("ORDER BY occurrences DESC");
		Query query = getEntityManager().createNativeQuery(selectSql.toString());
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(CommonStatistics.class));
		//封装返回数据
		return query.getResultList();
	}
	
	/**
	 * 分页查询同天同柜员号战法分析
	 * @param csp
	 * @param page
	 * @return
	 */
	public PageResult findCommonDayAndCounterNoByPage(CommonStatisticsParams csp) {
		String whichTable = caseManageService.getWhichTable(csp.getCaseId());
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT TO_CHAR(TDF.TRADE_DATE,'YYYY-MM-DD') AS ariseDate,");
		selectSql.append("TDF.TELLER_NUMBER AS tellerNumber,");
		selectSql.append("COUNT (*) AS occurrences,");
		selectSql.append("COUNT (DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) ariseObject,");
		selectSql.append("SUM (TDF.ACCOUNT_MONEY) AS sumMoney ");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append("FROM "+whichTable+" TDF where 1=1 AND IS_DELETE = 0  ");
		fromWhereSql.append("AND TDF.TELLER_NUMBER IS NOT NULL AND TDF.TRADE_DATE IS NOT NULL ");
		/* 案件id */
		if(StringUtils.isNotEmpty(csp.getCaseId())) {
			fromWhereSql.append("AND TDF.CASEMANAGE_ID = '"+csp.getCaseId()+"' ");
		}
		/* 收付标志 */
		if(StringUtils.isNotEmpty(csp.getSign())) {
			fromWhereSql.append("AND TDF.SIGN = '"+csp.getSign()+"' ");
		}
		/* 交易主题卡号 */
		fromWhereSql.append(" AND (1=0 ");
		if(StringUtils.isNotEmpty(csp.getCheckedAccount())) {
			if (csp.getCheckedAccount().split(",").length<1000) {
				String cards = "";
				for (String str : csp.getCheckedAccount().split(",")) {
					if (StringUtils.isNotEmpty(str)) {
						String accountBodyCard = str.split("_")[1];
						if (StringUtils.isNotEmpty(accountBodyCard)) {
							cards = cards + "'" + accountBodyCard + "',";
						}
					}
				}
				if (cards.endsWith(",")) {
					cards = cards.substring(0, cards.length() - 1);
				}
				if (cards.length() > 0) {
					fromWhereSql.append(" OR TDF.ACCOUNT_BODY_CARD IN (" + cards + ")");
				}
			}else {
				fromWhereSql.append(" or 1=1");
			}
		}
		fromWhereSql.append(" ) ");
		
		/* 交易开始日期 */
		if(StringUtils.isNotEmpty(csp.getStarTime())) {
			fromWhereSql.append(" AND TDF.TRADE_DATE >= to_date('"+csp.getStarTime()+"','yyyy-mm-dd hh24:mi:ss') ");
		}
		
		/* 交易结束日期 */
		if(StringUtils.isNotEmpty(csp.getEndTime())) {
			fromWhereSql.append(" AND TDF.TRADE_DATE <= to_date('"+csp.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
		}
		fromWhereSql.append("GROUP BY TO_CHAR(TDF.TRADE_DATE,'YYYY-MM-DD'),TDF.TELLER_NUMBER HAVING COUNT (*) > 1 AND COUNT(DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) >1 ");
        if (org.apache.commons.lang3.StringUtils.isNotBlank(csp.getSort())){
        	fromWhereSql.append(" order by "+csp.getSort()+" "+csp.getOrder());
        }else {
        	fromWhereSql.append("ORDER BY ariseDate asc");
        }
		long count = jdbcTemplate.queryForObject("select count(1) from ("+selectSql.toString()+fromWhereSql.toString()+") a", long.class);
		Query query = this.entityManager.createNativeQuery(selectSql.toString()+fromWhereSql.toString());
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(CommonStatistics.class));
		// 设置分页
		query.setFirstResult((csp.getPage() - 1) * csp.getRows());
		query.setMaxResults(csp.getRows());
		// 获取查询结果集
		List<DealFlow> list = query.getResultList();
		PageResult pageResult = new PageResult(count, list);
		return pageResult;
	}
	
	/**
	 * 查询所有同天同柜员号战法分析
	 * @param csp
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<CommonStatistics> findCommonDayAndCounterNoAll(CommonStatisticsParams csp) {
		String whichTable = caseManageService.getWhichTable(csp.getCaseId());
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT TO_CHAR(TDF.TRADE_DATE,'YYYY-MM-DD') AS ariseDate,");
		selectSql.append("TDF.TELLER_NUMBER AS tellerNumber,");
		selectSql.append("COUNT (*) AS occurrences,");
		selectSql.append("COUNT (DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) ariseObject,");
		selectSql.append("SUM (TDF.ACCOUNT_MONEY) AS sumMoney ");
		selectSql.append("FROM "+whichTable+" TDF where 1=1 AND IS_DELETE = 0  ");
		selectSql.append("AND TDF.TELLER_NUMBER IS NOT NULL AND TDF.TRADE_DATE IS NOT NULL ");
		appendWhereSql(csp, selectSql);
		selectSql.append("GROUP BY TO_CHAR(TDF.TRADE_DATE,'YYYY-MM-DD'),TDF.TELLER_NUMBER HAVING COUNT (*) > 1 AND COUNT(DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) >1 ");
		selectSql.append("ORDER BY occurrences DESC");
		
		Query query = getEntityManager().createNativeQuery(selectSql.toString());
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(CommonStatistics.class));
		//封装返回数据
		return query.getResultList();
	}
	
	/**
	 * 分页查询同天战法分析
	 * @param csp
	 * @param page
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public PageResult findCommonDayByPage(CommonStatisticsParams csp) {
		String whichTable = caseManageService.getWhichTable(csp.getCaseId());
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT TO_CHAR(TDF.TRADE_DATE,'YYYY-MM-DD') AS ariseDate,");
		selectSql.append("COUNT (*) AS occurrences,");
		selectSql.append("COUNT (DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) ariseObject,");
		selectSql.append("SUM (TDF.ACCOUNT_MONEY) AS sumMoney ");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append("FROM "+whichTable+" TDF where 1=1 AND IS_DELETE = 0  AND TDF.TRADE_DATE IS NOT NULL ");
		/* 案件id */
		if(StringUtils.isNotEmpty(csp.getCaseId())) {
			fromWhereSql.append("AND TDF.CASEMANAGE_ID ='"+csp.getCaseId()+"' ");
		}
		/* 收付标志 */
		if(StringUtils.isNotEmpty(csp.getSign())) {
			fromWhereSql.append("AND TDF.SIGN = ='"+csp.getSign()+"' ");
		}
		
		/* 交易主题卡号 */
		fromWhereSql.append(" AND (1=0 ");
		if(StringUtils.isNotEmpty(csp.getCheckedAccount())) {
			if (csp.getCheckedAccount().split(",").length<1000) {
				String cards = "";
				for (String str : csp.getCheckedAccount().split(",")) {
					if (StringUtils.isNotEmpty(str)) {
						String accountBodyCard = str.split("_")[1];
						if (StringUtils.isNotEmpty(accountBodyCard)) {
							cards = cards + "'" + accountBodyCard + "',";
						}
					}
				}
				if (cards.endsWith(",")) {
					cards = cards.substring(0, cards.length() - 1);
				}
				if (cards.length() > 0) {
					fromWhereSql.append(" OR TDF.ACCOUNT_BODY_CARD IN (" + cards + ")");
				}
			}else {
				fromWhereSql.append(" or 1=1");
			}
		}
		fromWhereSql.append(" ) ");
		
		/* 交易开始日期 */
		if(StringUtils.isNotEmpty(csp.getStarTime())) {
			fromWhereSql.append(" AND TDF.TRADE_DATE >= to_date('"+csp.getStarTime()+"','yyyy-mm-dd hh24:mi:ss') ");
		}
		
		/* 交易结束日期 */
		if(StringUtils.isNotEmpty(csp.getEndTime())) {
			fromWhereSql.append(" AND TDF.TRADE_DATE <= to_date('"+csp.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
		}
		fromWhereSql.append("GROUP BY TO_CHAR(TDF.TRADE_DATE,'YYYY-MM-DD') HAVING COUNT (*) > 1 AND COUNT(DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) >1 ");
        if (StringUtils.isNotEmpty(csp.getSort())){
        	fromWhereSql.append(" order by "+csp.getSort()+" "+csp.getOrder());
        }else {
        	fromWhereSql.append("ORDER BY ariseDate asc");
        }
        
        long count = jdbcTemplate.queryForObject("select count(1) from ("+selectSql.toString()+fromWhereSql.toString()+") a", long.class);
		Query query = this.entityManager.createNativeQuery(selectSql.toString()+fromWhereSql.toString());
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(CommonStatistics.class));
		// 设置分页
		if(null != csp.getPage()) {
			query.setFirstResult((csp.getPage() - 1) * csp.getRows());
			query.setMaxResults(csp.getRows());
		}
		// 获取查询结果集
		List<DealFlow> list = query.getResultList();
		PageResult pageResult = new PageResult(count, list);
		return pageResult;

	}
	
	/**
	 * 查询所有同天战法分析
	 * @param csp
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<CommonStatistics> findCommonDayAll(CommonStatisticsParams csp) {
		String whichTable = caseManageService.getWhichTable(csp.getCaseId());
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT TO_CHAR(TDF.TRADE_DATE,'YYYY-MM-DD') AS ariseDate,");
		selectSql.append("COUNT (*) AS occurrences,");
		selectSql.append("COUNT (DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) ariseObject,");
		selectSql.append("SUM (TDF.ACCOUNT_MONEY) AS sumMoney ");
		selectSql.append("FROM "+whichTable+" TDF where 1=1 AND IS_DELETE = 0  AND TDF.TRADE_DATE IS NOT NULL ");
		appendWhereSql(csp, selectSql);
		selectSql.append("GROUP BY TO_CHAR(TDF.TRADE_DATE,'YYYY-MM-DD') HAVING COUNT (*) > 1 AND COUNT(DISTINCT (case when TDF.ACCOUNT_BODY is not null then TDF.ACCOUNT_BODY else TDF.ACCOUNT_BODY_CARD end)) >1 ");
		selectSql.append("ORDER BY occurrences DESC");
		Query query = getEntityManager().createNativeQuery(selectSql.toString());
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(CommonStatistics.class));
		//封装返回数据
		return query.getResultList();
	}
	
	/**
	 * 根据案件id、交易对手账户分页查询交易详情
	 * @param csp
	 * @param page
	 * @return
	 * @throws ParseException 
	 */
	public Page<DealFlow> findBillDetailsByCaseIdAndAccountRivalNumber(CommonStatisticsParams csp, Pageable page,String sortName,String sortOrder) throws ParseException {
		String whichTable = caseManageService.getWhichTable(csp.getCaseId());
		List<Object> params = new LinkedList<Object>();
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT TDF.ACCOUNT_BODY_NUMBER AS accountBodyNumber,TDF.ACCOUNT_BODY AS accountBody,TDF.ACCOUNT_BODY_CARD AS accountBodyCard,TDF.ACCOUNT_RIVAL_NUMBER AS accountRivalNumber,");
		selectSql.append("TDF.ACCOUNT_RIVAL AS accountRival,TDF.ACCOUNT_RIVAL_CARD AS accountRivalCard,TDF.ACCOUNT_RIVAL_BANK AS accountRivalBank,TDF.ACCOUNT_ADDRESS AS accountAddress,");
		selectSql.append("TDF.SIGN AS sign,TDF.ACCOUNT_MONEY AS accountMoney,TDF.SURPLUS_MONEY AS surplusMoney,TDF.TRADE_DATE AS tradeDate,TDF.IP_ADDRESS AS ipAddress,");
		selectSql.append("TDF.ACCOUNT_MODE AS accountMode,TDF.CONTENT_ABSTRACT as contentAbstract,TDF.CURRENCY AS currency,TDF.REMARKS AS remarks ");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append("FROM "+whichTable+" TDF where 1=1 AND IS_DELETE = 0 ");
		/* 案件id */
		if(StringUtils.isNotEmpty(csp.getCaseId())) {
			fromWhereSql.append("AND TDF.CASEMANAGE_ID =? ");
			params.add(csp.getCaseId());
		}
		/* 收付标志 */
		if(StringUtils.isNotEmpty(csp.getSign())) {
			fromWhereSql.append("AND TDF.SIGN = ? ");
			params.add(csp.getSign());
		}
		
		/* 交易主题卡号 */
		fromWhereSql.append(" AND (1=0 ");
		if(StringUtils.isNotEmpty(csp.getCheckedAccount())) {
			if (csp.getCheckedAccount().split(",").length<1000) {
				String cards = "";
				for (String str : csp.getCheckedAccount().split(",")) {
					if (StringUtils.isNotEmpty(str)) {
						String accountBodyCard = str.split("_")[1];
						if (StringUtils.isNotEmpty(accountBodyCard)) {
							cards = cards + "'" + accountBodyCard + "',";
						}
					}
				}
				if (cards.endsWith(",")) {
					cards = cards.substring(0, cards.length() - 1);
				}
				if (cards.length() > 0) {
					fromWhereSql.append(" OR TDF.ACCOUNT_BODY_CARD IN (" + cards + ")");
				}
			}else {
				fromWhereSql.append(" or 1=1");
			}
		}
		fromWhereSql.append(" ) ");
		
		/* 交易开始日期 */
		if(StringUtils.isNotEmpty(csp.getStarTime())) {
			fromWhereSql.append(" AND TDF.TRADE_DATE >= to_date('"+csp.getStarTime()+"','yyyy-mm-dd hh24:mi:ss') AND TDF.TRADE_DATE IS NOT NULL ");
		}
		
		/* 交易结束日期 */
		if(StringUtils.isNotEmpty(csp.getEndTime())) {
			fromWhereSql.append(" AND TDF.TRADE_DATE <= to_date('"+csp.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') AND TDF.TRADE_DATE IS NOT NULL ");
		}
		
		/* 交易对手账号 */
		if(StringUtils.isNotEmpty(csp.getAccountRivalNumber())) {
			fromWhereSql.append("AND TDF.ACCOUNT_RIVAL_NUMBER = ? AND TDF.ACCOUNT_RIVAL_NUMBER IS NOT NULL ");
			params.add(csp.getAccountRivalNumber());
		}
		/* 交易对手户名 */
		if(StringUtils.isNotEmpty(csp.getAccountRival())) {
			fromWhereSql.append("AND TDF.ACCOUNT_RIVAL = ? AND TDF.ACCOUNT_RIVAL IS NOT NULL ");
			params.add(csp.getAccountRival());
		}
		/* 共同交易日期 */
		if(StringUtils.isNotEmpty(csp.getAriseDate())) {
			fromWhereSql.append(" AND TO_CHAR(TDF.TRADE_DATE,'YYYY-MM-DD')=?  AND TDF.TRADE_DATE IS NOT NULL ");
			params.add(csp.getAriseDate());
		}
		/* 共同交易金额 */
		if(StringUtils.isNotEmpty(csp.getAriseMoney())) {
			fromWhereSql.append(" AND TDF.ACCOUNT_MONEY = ? AND ACCOUNT_MONEY IS NOT NULL ");
			params.add(csp.getAriseMoney());
		}
		/* 共同机构号 */
		if(StringUtils.isNotEmpty(csp.getOrganizationNumber())) {
			fromWhereSql.append(" AND TDF.TRADING_ORGANIZATION_NUMBER = ? AND TDF.TRADING_ORGANIZATION_NUMBER IS NOT NULL ");
			params.add(csp.getOrganizationNumber());
		}
		/* 共同机构名称 */
		if(StringUtils.isNotEmpty(csp.getOrganizationName())) {
			fromWhereSql.append(" AND TDF.ACCOUNT_RIVAL_BANK = ? AND TDF.ACCOUNT_RIVAL_BANK IS NOT NULL ");
			params.add(csp.getOrganizationName());
		}
		/* 共同柜员号 */
		if(StringUtils.isNotEmpty(csp.getTellerNumber())) {
			fromWhereSql.append(" AND TDF.TELLER_NUMBER = ? AND TDF.TELLER_NUMBER IS NOT NULL ");
			params.add(csp.getTellerNumber());
		}
        String orderBySql = null;
        if (org.apache.commons.lang3.StringUtils.isNotBlank(sortName)){
            orderBySql =" order by "+sortName+" "+sortOrder;
        }else {
            orderBySql = "ORDER BY accountMoney DESC";
        }
		//封装返回数据
		return super.findPageBySql4GroupBy(DealFlow.class,selectSql.toString(), fromWhereSql.toString(), orderBySql, params, page);

	}
	
	/**
	 * 共同账户D3
	 * @param csp
	 * @return
	 */
    public Map<String, Object> getCommonAccountD3Result(CommonStatisticsParams csp){
		String whichTable = caseManageService.getWhichTable(csp.getCaseId());
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT (case when c.account_rival is not null then c.account_rival else c.account_rival_card end) as ACCOUNT_RIVAL,");
		selectSql.append("(case when c.account_body is not null then c.account_body else c.account_body_card end) as ACCOUNT_BODY,");
		selectSql.append("MAX(c.account_rival_card) as account_rival_card,MAX(c.account_body_card) as account_body_card,");
		selectSql.append("sum(abs(c.account_money)) SUMMONEY,COUNT(c.account_money) AS INNUMBER ");
		selectSql.append("FROM "+whichTable+" c ");
		selectSql.append("WHERE (case when c.account_rival is not null then c.account_rival else c.account_rival_card end) in ( ");
		selectSql.append("select b.rival from (select (case when a.account_body is not null then a.account_body else a.account_body_card end) as abody, ");
		selectSql.append("(case when a.account_rival is not null then a.account_rival else a.account_rival_card end) as rival from "+whichTable);
		selectSql.append(" a where ");
		/* 案件id */
		if(StringUtils.isNotEmpty(csp.getCaseId())) {
			selectSql.append("a.casemanage_id ='"+csp.getCaseId()+"' ");
		}
		/* 交易主题卡号 */
		String cards = "";
		if(StringUtils.isNotEmpty(csp.getCardList())){
            if (!"-1".equals(csp.getCardList())) {
            	String[] cc = csp.getCardList().split(",");
            	for(String c : cc ) {
            		for(int i = 0 ; i < c.split("_").length ; i ++) {
            			String d = StringUtils.isNotEmpty(c.split("_")[0]) ? c.split("_")[0] : c.split("_")[1];
            			cards = cards + "'" + d + "',";
            			break;
            		}
            	}
            	if (cards.endsWith(",")) {
					cards = cards.substring(0, cards.length() - 1);
				}
            	selectSql.append("and (case when a.account_body is not null then a.account_body else a.account_body_card end) in ( "+cards+" ) ");
            }
        }else{
        	selectSql.append("AND c.account_body_card in ( NULL ) ");
        }
		
		selectSql.append(" and (case when a.account_rival is not null then a.account_rival else a.account_rival_card end) is not null "); 
		selectSql.append(") b group by b.rival having count(distinct b.abody) >1) ");
		/* 交易主题卡号 */
		if(StringUtils.isNotEmpty(csp.getCardList())){
            if (!"-1".equals(csp.getCardList())) {
            	selectSql.append("and (case when c.account_body is not null then c.account_body else c.account_body_card end) in ( "+cards+" ) ");
            }
        }else{
        	selectSql.append("AND a.account_body_card in ( NULL ) ");
        }
		selectSql.append(" group by (case when c.account_rival is not null then c.account_rival else c.account_rival_card end),(case when c.account_body is not null then c.account_body else c.account_body_card end)");
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(selectSql.toString());
        
        StringBuffer selectSql2 = new StringBuffer();
        selectSql2.append("\r\n" + 
        		"select (case\r\n" + 
        		"         when a.account_body is not null then\r\n" + 
        		"          a.account_body\r\n" + 
        		"         else\r\n" + 
        		"          a.account_body_card\r\n" + 
        		"       end) as ACCOUNT_BODY,\r\n" + 
        		"       b.name as ACCOUNT_RIVAL,\r\n" + 
        		"       sum(abs(a.account_money)) as SUMMONEY,\r\n" + 
        		"       count(1) as INNUMBER\r\n" + 
        		"  from "+whichTable+" a,\r\n" + 
        		"       (select distinct (case\r\n" + 
        		"                          when account_body is not null then\r\n" + 
        		"                           account_body\r\n" + 
        		"                          else\r\n" + 
        		"                           account_body_card\r\n" + 
        		"                        end) as name,\r\n" + 
        		"                        account_body_card\r\n" + 
        		"          from "+whichTable+") b\r\n" + 
        		" where (case\r\n" + 
        		"         when a.account_body is not null then\r\n" + 
        		"          a.account_body\r\n" + 
        		"         else\r\n" + 
        		"          a.account_body_card\r\n" + 
        		"       end) in ("+cards+")\r\n" + 
        		"   and b.name in\r\n" + 
        		"       ("+cards+")\r\n" + 
        		"   and a.account_rival_card = b.account_body_card\r\n");
        /* 案件id */
		if(StringUtils.isNotEmpty(csp.getCaseId())) {
			selectSql2.append(" and a.casemanage_id ='"+csp.getCaseId()+"' ");
		}
        selectSql2.append(
        		" group by (case\r\n" + 
        		"            when a.account_body is not null then\r\n" + 
        		"             a.account_body\r\n" + 
        		"            else\r\n" + 
        		"             a.account_body_card\r\n" + 
        		"          end),\r\n" + 
        		"          b.name\r\n" + 
        		"") ; 
        		
		List<Map<String, Object>> mapsline = jdbcTemplate.queryForList(selectSql2.toString());
        Map<String, Object> hashMap = new HashMap<>();
        //第一步将左侧树勾选的主体封装上
        List<Map<String, Object>> nodes = new ArrayList<Map<String, Object>>();
        List<String> nodeNameList = new ArrayList<String>();
    	String sql1 = "select distinct (case when account_body is not null then account_body else account_body_card end) as NAME,max(ACCOUNT_BODY_CARD) as ACCOUNT_BODY_CARD,max(BODY_TYPE) as BODY_TYPE from "+whichTable+" where is_delete=0 and casemanage_id='"+csp.getCaseId()+"' ";
    	if(!"-1".equals(csp.getCardList())) {
    		sql1+=" and (case when account_body is not null then account_body else account_body_card end) in ("+cards+") ";
    	}
    	sql1+=" group by (case when account_body is not null then account_body else account_body_card end) ";
		List<Map<String, Object>> bodys = jdbcTemplate.queryForList(sql1);
    	if(bodys!=null && bodys.size()>0) {
    		for(Map<String, Object> body:bodys) {
    			String nodeName = (body.get("NAME")==null?"":body.get("NAME").toString());
    			String nodeCard = body.get("ACCOUNT_BODY_CARD")==null?"":body.get("ACCOUNT_BODY_CARD").toString();
    			String isSpecial = (body.get("BODY_TYPE")!=null && "嫌疑人".equals(body.get("BODY_TYPE").toString()))?"1":"0";
    			HashMap<String, Object> node = new HashMap<>();
    		    node.put("id",UUID.randomUUID().toString());
    		    node.put("name",nodeName);
    		    node.put("card",nodeCard);
    		    node.put("isSpecial", isSpecial);
		    	node.put("width",100);
		    	node.put("height",100);
		    	if(com.mairuide._frame.utils.StringUtils.isNotEmpty(nodeName) && com.mairuide._frame.utils.StringUtils.isNotEmpty(nodeName)) {
    		    	if(nodeName.contains("企业") || nodeName.contains("公司") 
    		    			|| nodeName.contains("支付宝") || nodeName.contains("财付通")
    		    			|| nodeCard.length() == 15 || nodeCard.length() == 17) {
    		    		if(isSpecial.equals("1")) {
    		    			//嫌疑单位账户
    		        		node.put("image","/plugins/d3/image/q2.png");
    		    		}else {
    		    			node.put("image","/plugins/d3/image/q1.png");
    		    		}
    		    	}else {
    		    		if(isSpecial.equals("1")) {
    		    			//嫌疑人账户
    		        		node.put("image","/plugins/d3/image/r2.png");
    		    		}else {
    		    			node.put("image","/plugins/d3/image/r1.png");
    		    		}
    		    	}
    		    }else {
    		    	node.put("image","/plugins/d3/image/r1.png");
    		    }
		    	nodes.add(node);
		    	nodeNameList.add(nodeName);
    		}
    	}
        
        if (maps.size()>0){
            List<Map<String, Object>> links = new ArrayList<Map<String, Object>>();
            for (int i=0;i<maps.size();i++){
            	/** 封装交易对手D3 **/
        		Map<String, Object> map = maps.get(i);
        		//封装交易对手 多循环一次是为了先封装交易主体，再封装对手
        		String nodeCard2 = map.get("ACCOUNT_BODY_CARD")==null ? "": map.get("ACCOUNT_BODY_CARD").toString();
        		String nodeName2 = map.get("ACCOUNT_RIVAL")==null?"" : map.get("ACCOUNT_RIVAL").toString();
        		if (!nodeNameList.contains(nodeName2)){
        		    HashMap<String, Object> node = new HashMap<>();
        		    node.put("id",UUID.randomUUID().toString());
        		    node.put("name",nodeName2);
        		    node.put("card",nodeCard2);
        		    node.put("isSpecial", "0");
        			node.put("width",60);
        			node.put("height",60);
        			// 判断账户名称是否包含企业或公司
        		    if(com.mairuide._frame.utils.StringUtils.isNotEmpty(nodeName2) && com.mairuide._frame.utils.StringUtils.isNotEmpty(nodeCard2)) {
        		    	if(nodeName2.contains("企业") || nodeName2.contains("公司") 
        		    			|| nodeName2.contains("支付宝") || nodeName2.contains("财付通")
        		    			|| nodeCard2.length() == 15 || nodeCard2.length() == 17) {
        		    		node.put("image","/plugins/d3/image/q1.png");
        		    	}else {
        		    		node.put("image","/plugins/d3/image/r1.png");
        		    	}
        		    }else {
        		    	node.put("image","/plugins/d3/image/r1.png");
        		    }
        		    nodes.add(node);
        		    nodeNameList.add(nodeName2);
        		}
            }
            for (int i=0;i<maps.size();i++){
            	/** 封装交易主体交易对手line **/
            	encapsulationLine(maps, nodes, links, i);
            }
            for (int i=0;i<mapsline.size();i++){
            	/** 封装主体之间的交易 **/
        		Map<String, Object> map = maps.get(i);
        		HashMap<String, Object> link = new HashMap<>();
        		String nodeName = map.get("ACCOUNT_BODY")==null ? "" : (map.get("ACCOUNT_BODY").toString());
        		String nodeName2 = map.get("ACCOUNT_RIVAL")==null ? "" : (map.get("ACCOUNT_RIVAL").toString());
        		if(nodeName.equalsIgnoreCase(nodeName2)) continue;
        		for (Map<String, Object> m:nodes){
        		    if (nodeName2.equals((String)m.get("name"))){
        		    	link.put("node1",m.get("id"));
        		    }
        		    if (nodeName.equals((String)m.get("name"))){
        		    	link.put("node2",m.get("id"));
        		    }
        		    link.put("name","交易"+map.get("INNUMBER")+"次,交易"+map.get("SUMMONEY")+"元");
        		}
        		if(!link.keySet().contains("node1") || !link.keySet().contains("node2"))
        			continue;
        		links.add(link);
            }
            
            hashMap.put("links",links);
            hashMap.put("nodes",nodes);
        }

        return hashMap;
    }

    
	
    /**
	 * 共同户名D3
	 * @param csp
	 * @return
	 */
    public Map<String, Object> getCommonAccountRivalD3Result(CommonStatisticsParams csp){
		String whichTable = caseManageService.getWhichTable(csp.getCaseId());
    	StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT T1.ACCOUNT_BODY_CARD AS ACCOUNT_BODY_CARD,");
		selectSql.append("MAX(T1.ACCOUNT_BODY) AS ACCOUNT_BODY,");
		selectSql.append("MAX(T1.BODY_TYPE) AS BODY_TYPE,");
		selectSql.append("T1.ACCOUNT_RIVAL AS ACCOUNT_RIVAL,");
		selectSql.append("MAX(T1.ACCOUNT_RIVAL_CARD) AS ACCOUNT_RIVAL_CARD,");
		selectSql.append("SUM(account_money) SUMMONEY,");
		selectSql.append("COUNT(1) AS INNUMBER ");
		selectSql.append("FROM "+whichTable+" T1 ");
		selectSql.append("WHERE T1.ACCOUNT_RIVAL IN ");
		selectSql.append("(SELECT TDF.ACCOUNT_RIVAL ");
		selectSql.append("FROM "+whichTable+" TDF where 1 = 1 AND IS_DELETE = 0 ");
		/* 案件id */
		if(StringUtils.isNotEmpty(csp.getCaseId())) {
			selectSql.append("AND TDF.CASEMANAGE_ID ="+csp.getCaseId());
		}
		/* 交易主题卡号 */
		if(StringUtils.isNotEmpty(csp.getCardList())){
            if (!"-1".equals(csp.getCardList())) {
            	selectSql.append("AND TDF.account_body_card in ( "+csp.getCardList()+" ) ");
            }
        }else{
        	selectSql.append("AND TDF.account_body_card in ( NULL ) ");
        }
		selectSql.append(" GROUP BY TDF.ACCOUNT_RIVAL "); 
		selectSql.append(" HAVING COUNT(DISTINCT TDF.ACCOUNT_BODY_CARD) > 1) ");
		selectSql.append(" AND T1.IS_DELETE = 0 ");
		/* 案件id */
		if(StringUtils.isNotEmpty(csp.getCaseId())) {
			selectSql.append("AND T1.CASEMANAGE_ID ='"+csp.getCaseId()+"' ");
		}
		/* 交易开始日期 */
		if(StringUtils.isNotEmpty(csp.getStarTime())) {
			selectSql.append(" AND T1.TRADE_DATE >= to_date('"+csp.getStarTime()+"','yyyy-mm-dd hh24:mi:ss') ");
		}
		/* 交易结束日期 */
		if(StringUtils.isNotEmpty(csp.getEndTime())) {
			selectSql.append(" AND T1.TRADE_DATE <= to_date('"+csp.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
		}
		/* 模糊查询共同交易对手账号*/
		if(StringUtils.isNotEmpty(csp.getCommonAccountRivalNumber())) {
			selectSql.append("AND T1.ACCOUNT_RIVAL_NUMBER like '%"+csp.getCommonAccountRivalNumber()+"%' ");
		}
		if(StringUtils.isNotEmpty(csp.getCardList())){
            if (!"-1".equals(csp.getCardList())) {
            	selectSql.append("AND T1.account_body_card in ( "+csp.getCardList()+" ) ");
            }
        }else{
        	selectSql.append("AND T1.account_body_card in ( NULL ) ");
        }
		/* 模糊查询共同交易对手名称*/
		if(StringUtils.isNotEmpty(csp.getAccountRival())) {
			selectSql.append("AND T1.ACCOUNT_RIVAL like '%"+csp.getAccountRival()+"%' ");
		}
		selectSql.append(" GROUP BY T1.ACCOUNT_BODY_CARD,T1.ACCOUNT_RIVAL");
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(selectSql.toString());
        Map<String, Object> hashMap = new HashMap<>();
        if (maps.size()>0){
        	
        	List<String> selectedCards = Arrays.asList(csp.getCheckedAccount().split(","));//所有已选单位的合集 -1代表全选
            
            List<Map<String, Object>> nodes = new ArrayList<Map<String, Object>>();
            List<Map<String, Object>> links = new ArrayList<Map<String, Object>>();
            List<String> nodeNameList = new ArrayList<String>();
            for (int i=0;i<maps.size();i++){
            	/** 封装交易主题D3 **/
            	encapsulationAccountBodyD3(csp, maps, selectedCards, nodes, nodeNameList, i);
            }
            for (int i=0;i<maps.size();i++){
            	/** 封装交易对手D3 **/
            	encapsulationAccountRivalD3(maps, nodes, nodeNameList, i);
            }
            for (int i=0;i<maps.size();i++){
            	/** 封装交易主体交易对手line **/
            	encapsulationLine(maps, nodes, links, i);
            }
            hashMap.put("links",links);
            hashMap.put("nodes",nodes);
        }

        return hashMap;
    }
    
	
	/**
	 * appendWhereSql4Params
	 * @param csp
	 * @param params
	 * @param fromWhereSql
	 */
	private void appendWhereSql4Params(CommonStatisticsParams csp, List<Object> params, StringBuffer fromWhereSql) {
		/* 案件id */
		if(StringUtils.isNotEmpty(csp.getCaseId())) {
			fromWhereSql.append("AND TDF.CASEMANAGE_ID =? ");
			params.add(csp.getCaseId());
		}
		/* 收付标志 */
		if(StringUtils.isNotEmpty(csp.getSign())) {
			fromWhereSql.append("AND TDF.SIGN = ? ");
			params.add(csp.getSign());
		}
		
		/* 交易主题卡号 */
		fromWhereSql.append(" AND (1=0 ");
		if(StringUtils.isNotEmpty(csp.getCheckedAccount())) {
			if (csp.getCheckedAccount().split(",").length<1000) {
				String cards = "";
				for (String str : csp.getCheckedAccount().split(",")) {
					if (StringUtils.isNotEmpty(str)) {
						String accountBodyCard = str.split("_")[1];
						if (StringUtils.isNotEmpty(accountBodyCard)) {
							cards = cards + "'" + accountBodyCard + "',";
						}
					}
				}
				if (cards.endsWith(",")) {
					cards = cards.substring(0, cards.length() - 1);
				}
				if (cards.length() > 0) {
					fromWhereSql.append(" OR TDF.ACCOUNT_BODY_CARD IN (" + cards + ")");
				}
			}else {
				fromWhereSql.append(" or 1=1");
			}
		}
		fromWhereSql.append(" ) ");
		
		/* 交易开始日期 */
		if(StringUtils.isNotEmpty(csp.getStarTime())) {
			fromWhereSql.append(" AND TDF.TRADE_DATE >= to_date('"+csp.getStarTime()+"','yyyy-mm-dd hh24:mi:ss') ");
		}
		
		/* 交易结束日期 */
		if(StringUtils.isNotEmpty(csp.getEndTime())) {
			fromWhereSql.append(" AND TDF.TRADE_DATE <= to_date('"+csp.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
		}
	}
	
	/**
	 * appendWhereSql
	 * @param csp
	 * @param selectSql
	 */
	private void appendWhereSql(CommonStatisticsParams csp, StringBuffer selectSql) {
		/* 案件id */
		if(StringUtils.isNotEmpty(csp.getCaseId())) {
			selectSql.append("AND TDF.CASEMANAGE_ID ='"+csp.getCaseId()+"' ");
		}
		/* 收付标志 */
		if(StringUtils.isNotEmpty(csp.getSign())) {
			selectSql.append(" AND TDF.SIGN>='"+csp.getSign()+"' ");
		}
		
		/* 交易主题卡号 */
		selectSql.append(" and (1 = 0 ");
		if(StringUtils.isNotEmpty(csp.getCheckedAccount())) {
			for(String str : csp.getCheckedAccount().split(",")) {
				if(StringUtils.isNotEmpty(str)) {
					String accountBodyCard = str.split("_")[1];
					if(StringUtils.isNotEmpty(accountBodyCard)) {
						selectSql.append(" or TDF.ACCOUNT_BODY_CARD = '"+accountBodyCard+"' ");
					}
				}
			}
		}
		selectSql.append(" )");
		
		/* 交易开始日期 */
		if(StringUtils.isNotEmpty(csp.getStarTime())) {
			selectSql.append(" AND TDF.TRADE_DATE >= to_date('"+csp.getStarTime()+"','yyyy-mm-dd hh24:mi:ss') ");
		}
		
		/* 交易结束日期 */
		if(StringUtils.isNotEmpty(csp.getEndTime())) {
			selectSql.append(" AND TDF.TRADE_DATE <= to_date('"+csp.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
		}
	}
	
	/**
	 * 封装交易主体交易对手line
	 * @param maps
	 * @param nodes
	 * @param links
	 * @param i
	 */
	private void encapsulationLine(List<Map<String, Object>> maps, List<Map<String, Object>> nodes,
			List<Map<String, Object>> links, int i) {
		Map<String, Object> map = maps.get(i);
		HashMap<String, Object> link = new HashMap<>();
		String nodeName = map.get("ACCOUNT_BODY")==null ? (map.get("ACCOUNT_BODY_CARD").toString()) : (map.get("ACCOUNT_BODY").toString());
		String nodeName2 = map.get("ACCOUNT_RIVAL")==null ? (map.get("ACCOUNT_RIVAL_CARD").toString()) : (map.get("ACCOUNT_RIVAL").toString());
		for (Map<String, Object> m:nodes){
		    if (nodeName2.equals((String)m.get("name"))){
		    	link.put("node1",m.get("id"));
		    }
		    if (nodeName.equals((String)m.get("name"))){
		    	link.put("node2",m.get("id"));
		    }
		    link.put("name","交易"+map.get("INNUMBER")+"次,交易"+map.get("SUMMONEY")+"元");
		}
		if(!link.keySet().contains("node1") || !link.keySet().contains("node2"))
			return;
		links.add(link);
	}
	
	/**
	 * 封装交易对手D3
	 * @param maps
	 * @param nodes
	 * @param nodeNameList
	 * @param i
	 */
	private void encapsulationAccountRivalD3(List<Map<String, Object>> maps, List<Map<String, Object>> nodes,
			List<String> nodeNameList, int i) {
		/** 封装交易对手D3 **/
		Map<String, Object> map = maps.get(i);
		//封装交易对手 多循环一次是为了先封装交易主体，再封装对手
		String nodeName2 = map.get("ACCOUNT_RIVAL")==null ? (map.get("ACCOUNT_RIVAL_CARD").toString()) : (map.get("ACCOUNT_RIVAL").toString());
		String nodeCard2 = map.get("ACCOUNT_RIVAL_CARD")==null?"" : map.get("ACCOUNT_RIVAL_CARD").toString();
		if (!nodeNameList.contains(nodeName2)){
		    HashMap<String, Object> node = new HashMap<>();
		    node.put("id",UUID.randomUUID().toString());
		    node.put("name",nodeName2);
		    node.put("card",nodeCard2);
		    node.put("isSpecial", "0");
			node.put("width",60);
			node.put("height",60);
			// 判断账户名称是否包含企业或公司
		    if(com.mairuide._frame.utils.StringUtils.isNotEmpty(nodeName2) && com.mairuide._frame.utils.StringUtils.isNotEmpty(nodeCard2)) {
		    	if(nodeName2.contains("企业") || nodeName2.contains("公司") 
		    			|| nodeName2.contains("支付宝") || nodeName2.contains("财付通")
		    			|| nodeCard2.length() == 15 || nodeCard2.length() == 17) {
		    		node.put("image","/plugins/d3/image/q1.png");
		    	}else {
		    		node.put("image","/plugins/d3/image/r1.png");
		    	}
		    }else {
		    	node.put("image","/plugins/d3/image/r1.png");
		    }
			
		    nodes.add(node);
		    nodeNameList.add(nodeName2);
		}
	}
	/**
	 * 封装交易主题D3
	 * @param csp
	 * @param maps
	 * @param selectedCards
	 * @param nodes
	 * @param nodeNameList
	 * @param i
	 */
	private void encapsulationAccountBodyD3(CommonStatisticsParams csp, List<Map<String, Object>> maps,
			List<String> selectedCards, List<Map<String, Object>> nodes, List<String> nodeNameList, int i) {
		Map<String,Object> map = maps.get(i);
		/** 封装交易主题D3 **/
		String nodeName = map.get("ACCOUNT_BODY") == null ? (map.get("ACCOUNT_BODY_CARD").toString()) : (map.get("ACCOUNT_BODY").toString());
		String nodeCard = map.get("ACCOUNT_BODY_CARD") == null ? "" : map.get("ACCOUNT_BODY_CARD").toString();
		String isSpecial = (map.get("BODY_TYPE") != null && "嫌疑人".equals(map.get("BODY_TYPE").toString())) ? "1" : "0";
		if (!nodeNameList.contains(nodeName)){
		    HashMap<String, Object> node = new HashMap<>();
		    node.put("id",UUID.randomUUID().toString());
		    node.put("name",nodeName);
		    node.put("card",nodeCard);
		    node.put("isSpecial", isSpecial);
		    if("-1".equals(csp.getCheckedAccount()) || selectedCards.contains("'"+nodeCard+"'")) {
		    	node.put("width",100);
		    	node.put("height",100);
		    }else {
		    	node.put("width",60);
		    	node.put("height",60);
		    }
		    // 判断账户名称是否包含企业或公司
		    if(com.mairuide._frame.utils.StringUtils.isNotEmpty(nodeName) && com.mairuide._frame.utils.StringUtils.isNotEmpty(nodeCard)) {
		    	if(nodeName.contains("企业") || nodeName.contains("公司") 
		    			|| nodeName.contains("支付宝") || nodeName.contains("财付通")
		    			|| nodeCard.length() == 15 || nodeCard.length() == 17 ) {
		    		if(isSpecial.equals("1")) {
		    			//嫌疑单位账户
		        		node.put("image","/plugins/d3/image/q2.png");
		    		}else {
		    			node.put("image","/plugins/d3/image/q1.png");
		    		}
		    	}else {
		    		if(isSpecial.equals("1")) {
		    			//嫌疑人账户
		        		node.put("image","/plugins/d3/image/r2.png");
		    		}else {
		    			node.put("image","/plugins/d3/image/r1.png");
		    		}
		    	}
		    }else {
		    	node.put("image","/plugins/d3/image/r1.png");
		    }
		    nodes.add(node);
		    nodeNameList.add(nodeName);
		}
	}
}
